//
//  SQManager.m
//  SQLiteForOCModel
//
//  Created by 唐健 on 2018/10/4.
//  Copyright © 2018年 唐健. All rights reserved.
//

#import "SQManager.h"
#import <FMDatabase.h>
#import <FMDatabaseQueue.h>
#import <FMDatabaseAdditions.h>

#define sqModelTableKey @"sqModelTableKey"

@interface SQManager ()

@property (nonatomic,copy) NSString *sqPath;
//@property (nonatomic,strong) FMDatabase *sqDB;

@property (nonatomic,strong) FMDatabaseQueue *sqQueue;

@end

@implementation SQManager


static SQManager *manager = nil;
+ (instancetype)shareInstance{
    if(manager == nil){
        manager = [SQManager new];
        [manager setupSQDataBase];
    }
    return manager;
}

- (NSString *)sqPath{
    if(_sqPath == nil){
        NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
        NSString *sqPath = [doc stringByAppendingPathComponent:@"user_sqModel.sqlite"];
        _sqPath = sqPath;
    }
    NSLog(@"数据库路径\n%@\n", _sqPath);
    return _sqPath;
}

- (void)setupSQDataBase{
    NSAssert([self beginConnection], @"数据库初始化失败！");
}
- (BOOL)beginConnection{
    
    self.sqQueue = [FMDatabaseQueue databaseQueueWithPath:self.sqPath];
    
   __block FMDatabase *sqDB = nil;
    [self.sqQueue inDatabase:^(FMDatabase * _Nonnull db) {
        sqDB = db;
    }];
    if ([sqDB open]) {
        return YES;
    }else{
        return NO;
    }
}

/**
 创建表
 */
- (BOOL)createTableForTableName:(NSString *)tableName forKeyValues:(NSDictionary *)keyValues inDatabase:(FMDatabase *)db{
    if ([db open]) {
        /**
         字段类型
         SQLite将数据划分为以下几种存储类型：
         integer : 整型值
         real : 浮点值
         text : 文本字符串
         blob : 二进制数据（比如文件）
         注意：实际上SQLite是无类型的，就算声明为integer类型，还是能存储字符串文本（主键除外）
         建表时声明啥类型或者不声明类型都可以，也就意味着创表语句可以这么写：
         create table t_student(name, age);
         */
        
        NSString *keysSQStr = [keyValues.allKeys componentsJoinedByString:@","];
        
        NSString *sqlStr = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (primary_id integer primary key,%@);",tableName,keysSQStr];
        
        BOOL result = [db executeUpdate:sqlStr];
        if (result) {
            NSLog(@"创表成功! tableName ->%@",tableName);
        }else
        {
            NSLog(@"创表失败! tableName ->%@",tableName);
        }
        return result;
    }else{
        NSLog(@"数据库打开失败！");
        return NO;
    }
}

/**
 添加新的字段到已存在的表中
 */
- (BOOL)addColumnWithKeys:(NSArray *)keys inTableWithName:(NSString*)tableName inDatabase:(FMDatabase *)db{
    BOOL worked = YES;
    
//    NSString *keysSQStr = [keys componentsJoinedByString:@" , "];
    
    for(NSString *newKey in keys){
        NSString *alertStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ ;",tableName,newKey];
        worked = [db executeUpdate:alertStr];
        if(worked){
            NSLog(@"插入Key成功 !\nnewKey ->%@",newKey);
        }else{
            NSLog(@"插入Key失败 !\nnewKey ->%@",newKey);
            worked = NO;
            break;
        }
    }

    return worked;
}

/**
 返回当前表不包含的键名
 */
- (NSArray *)columnNameNotExists:(NSArray *)keys inTableWithName:(NSString*)tableName inDatabase:(FMDatabase *)db{
    
    NSMutableArray *columnNames = [NSMutableArray new];
    NSMutableArray *notExistsKeys = keys.mutableCopy;
    
    FMResultSet *rs = [db getTableSchema:tableName];
    while ([rs next]) {
        NSString *name = [rs stringForColumn:@"name"];
        if(name.length > 0)[columnNames addObject:name];
    }
    [rs close];
    
    [notExistsKeys removeObjectsInArray:columnNames];
    
    return notExistsKeys;
}


/**
 插入对应键值的条目
 */
- (BOOL)insertSQModelKeyValues:(NSDictionary *)keyValues inTableWithName:(NSString*)tableName inDatabase:(FMDatabase *)db{
    
    NSMutableDictionary *selectDic = [NSMutableDictionary dictionary];
    [selectDic setObject:[keyValues objectForKey:SQModel_idMarkKey] forKey:SQModel_idMarkKey];
    
    NSArray *resDictArr = [self selectSQModelKeyValues:selectDic inTableWithName:tableName inDatabase:db];
    if(resDictArr.count > 0){
        BOOL delete = [self deleteSQModelKeyValues:selectDic inTableWithName:tableName inDatabase:db];
        if(delete == NO){
            NSLog(@"删除原表对应主键数据失败！selectDic ->%@",selectDic);
            return NO;
        }
    }
    
    
    NSArray *allKeys = keyValues.allKeys;
    NSArray *allValues = keyValues.allValues;
    
    NSMutableArray *valuesStrArr = [NSMutableArray array];
    for(int i = 0;i < allKeys.count ; i++){
        [valuesStrArr addObject:@"?"];
    }
    NSString *valuesPlaceHolderStr = [valuesStrArr componentsJoinedByString:@","];
    NSString *keysPlaceHolderStr = [allKeys componentsJoinedByString:@","];
    
    NSString *insertSQStr = [NSString stringWithFormat:@"INSERT INTO %@(%@)values(%@);",tableName,keysPlaceHolderStr,valuesPlaceHolderStr];
   
    BOOL result = [db executeUpdate:insertSQStr withArgumentsInArray:allValues];
    
    if(result == NO){
        NSLog(@"插入新数据失败！keyValues ->%@",keyValues);
    }
    
    return result;
}



/**
 查找对应键值的条目
 */
- (NSArray *)selectSQModelKeyValues:(NSDictionary *)keyValues inTableWithName:(NSString*)tableName inDatabase:(FMDatabase *)db{
    
    NSArray *allKeys = keyValues.allKeys;
    NSArray *allValues = keyValues.allValues;
    NSMutableArray *valuesStrArr = [NSMutableArray array];
    
    for(NSString *key in allKeys){
        [valuesStrArr addObject:[NSString stringWithFormat:@"%@ = (?)",key]];
    }
    
    
    NSString *valuesPlaceHolderStr = [valuesStrArr componentsJoinedByString:@" and "];
    
    NSString *sql = [NSString stringWithFormat:@"select * from %@ where %@;",tableName, valuesPlaceHolderStr];
    FMResultSet *res = [db executeQuery:sql withArgumentsInArray:allValues];
    
    NSMutableArray *resDicArr = [NSMutableArray array];
    while ([res next]) {
       NSDictionary *temp = [res resultDictionary];
        [resDicArr addObject:temp];
    }
    [res close];
    
    NSLog(@"索引键值 ->%@\n索引结果 ->count = %zd ",keyValues,resDicArr.count);
    
    return resDicArr;
}


/**
 删除对应键值的条目
 */
- (BOOL)deleteSQModelKeyValues:(NSDictionary *)keyValues inTableWithName:(NSString*)tableName inDatabase:(FMDatabase *)db{
    
    NSArray *allKeys = keyValues.allKeys;
    NSArray *allValues = keyValues.allValues;
    NSMutableArray *allSpendingkeys = [NSMutableArray array];
    
    for(NSString *key in allKeys){
        [allSpendingkeys addObject:[key stringByAppendingString:@" = (?)"]];
    }
    
    NSString *keysPlaceHolderStr = [allSpendingkeys componentsJoinedByString:@","];
    NSString *deleteSql = [NSString stringWithFormat:
                           @"delete from %@ WHERE %@;",tableName, keysPlaceHolderStr];
    
    BOOL result = [db executeUpdate:deleteSql withArgumentsInArray:allValues];
    
    return result;
}




#pragma mark - 对外模型的接口

/**
 对模型的接口
 */
+ (void)SQDB_INSERT_WithkeyAndValue:(NSDictionary *)SQModel_keyAndValues insertResult:(void (^)(BOOL isSuccess))result{
    
    SQManager *manager = [self shareInstance];
    
    dispatch_async(dispatch_get_global_queue(0, 0), ^{
        [manager.sqQueue inDatabase:^(FMDatabase * _Nonnull db) {
            BOOL isSuccess = NO;
            NSString *tableName = [SQModel_keyAndValues valueForKey:SQModel_tabNameKey];
            BOOL tabExist = [db tableExists:tableName];
            if(tabExist == YES){
                NSArray *notExistKeys = [manager columnNameNotExists:SQModel_keyAndValues.allKeys inTableWithName:tableName inDatabase:db];
                if(notExistKeys.count > 0){
                    isSuccess = [manager addColumnWithKeys:notExistKeys inTableWithName:tableName inDatabase:db];
                }else{
                    isSuccess = YES;
                }
            }else{
                isSuccess = [manager createTableForTableName:tableName forKeyValues:SQModel_keyAndValues inDatabase:db];
            }
            if(isSuccess == YES){
                isSuccess = [manager insertSQModelKeyValues:SQModel_keyAndValues inTableWithName:tableName inDatabase:db];
            }
            
            dispatch_async(dispatch_get_main_queue(), ^{
                result(isSuccess);
            });
            
        }];
    });
}

+ (void)SQDB_DELETE_WithSQModel_id:(NSString *)SQModel_id tabName:(NSString *)tabName deleteResult:(void (^)(BOOL isSuccess))result{
    SQManager *manager = [self shareInstance];
    
    dispatch_async(dispatch_get_global_queue(0, 0), ^{
        
        [manager.sqQueue inDatabase:^(FMDatabase * _Nonnull db) {
            BOOL isSuccess = NO;
            NSString *tableName = tabName;
            
            BOOL tabExist = [db tableExists:tableName];
            if(tabExist == YES){
                NSMutableDictionary *deleteDic = [NSMutableDictionary dictionary];
                [deleteDic setObject:SQModel_id forKey:SQModel_idMarkKey];
                
                isSuccess = [manager deleteSQModelKeyValues:deleteDic inTableWithName:tableName inDatabase:db];
            }else{
                isSuccess = YES;
            }
            
            dispatch_async(dispatch_get_main_queue(), ^{
                result(isSuccess);
            });
            
        }];
    });
}

+ (void)SQDB_DELETE_WithSQModelWithTabName:(NSString *)tabName deleteResult:(void (^)(BOOL isSuccess))result{
    SQManager *manager = [self shareInstance];
    
    dispatch_async(dispatch_get_global_queue(0, 0), ^{
        [manager.sqQueue inDatabase:^(FMDatabase * _Nonnull db) {
            
            BOOL isSuccess = NO;
            NSString *tableName = tabName;
            
            BOOL tabExist = [db tableExists:tableName];
            if(tabExist == YES){
                NSMutableDictionary *deleteDic = [NSMutableDictionary dictionary];
                [deleteDic setObject:tabName forKey:SQModel_tabNameKey];
                
                isSuccess = [manager deleteSQModelKeyValues:deleteDic inTableWithName:tableName inDatabase:db];
            }else{
                isSuccess = YES;
            }
            
            dispatch_async(dispatch_get_main_queue(), ^{
                result(isSuccess);
            });
        }];
    });
}


+ (void)SQDB_SELECT_WithSQModel_id:(NSString *)SQModel_id tabName:(NSString *)tabName selectResult:(void (^)(NSDictionary *sqDict))result{
    SQManager *manager = [self shareInstance];
    
    dispatch_async(dispatch_get_global_queue(0, 0), ^{
        
        [manager.sqQueue inDatabase:^(FMDatabase * _Nonnull db) {
            NSArray *resDictArr = nil;
            NSString *tableName = tabName;
            
            BOOL tabExist = [db tableExists:tableName];
            if(tabExist == YES){
                NSMutableDictionary *selectDic = [NSMutableDictionary dictionary];
                [selectDic setObject:SQModel_id forKey:SQModel_idMarkKey];
                resDictArr = [manager selectSQModelKeyValues:selectDic inTableWithName:tableName inDatabase:db];
            }
            dispatch_async(dispatch_get_main_queue(), ^{
                result(resDictArr.lastObject);
            });
        }];
    });
    
}

+ (void)SQDB_SELECT_AllSQModelTabName:(NSString *)tabName selectResult:(void (^)(NSArray *dictArr))result{
    SQManager *manager = [self shareInstance];
    
    dispatch_async(dispatch_get_global_queue(0, 0), ^{
        [manager.sqQueue inDatabase:^(FMDatabase * _Nonnull db) {
            NSArray *resDictArr = nil;
            NSString *tableName = tabName;
            
            BOOL tabExist = [db tableExists:tableName];
            if(tabExist == YES){
                NSMutableDictionary *selectDic = [NSMutableDictionary dictionary];
                [selectDic setObject:tabName forKey:SQModel_tabNameKey];
                resDictArr = [manager selectSQModelKeyValues:selectDic inTableWithName:tableName inDatabase:db];
            }
            
            dispatch_async(dispatch_get_main_queue(), ^{
                result(resDictArr);
            });
        }];
    });
    
}





#pragma noUseToolFunctions


//- (void)toolFunction:(NSDictionary *)keyValues{
//        [keyValues enumerateKeysAndObjectsUsingBlock:^(id  _Nonnull key, id  _Nonnull obj, BOOL * _Nonnull stop) {
//            if([obj isKindOfClass:[NSNumber class]]){
//                const char * objCType = [((NSNumber*)obj) objCType];
//                if (strcmp(objCType, @encode(float)) == 0) {
//                    NSLog(@"字典中key=%@的值是float类型,值为%f",key,[obj floatValue]);
//                }
//
//                if (strcmp(objCType, @encode(double))  == 0) {
//                    NSLog(@"字典中key=%@的值是double类型,值为%f",key,[obj doubleValue]);
//                }
//
//                if (strcmp(objCType, @encode(int))  == 0) {
//                    NSLog(@"字典中key=%@的值是int类型,值为%d",key,[obj intValue]);
//                }
//
//                if (strcmp(objCType, @encode(BOOL)) == 0) {
//                    NSLog(@"字典中key=%@的值是bool类型,值为%i",key,[obj boolValue]);
//                }
//            }
//        }];
//
//
//
//
//    }

@end
